﻿<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta name="robots" content="all" />
<meta name="author" content="" />
<meta name="Copyright" content="banquan" />
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<meta name="description" content="" />
<meta content="" name="keywords" />
<title>学术成果</title>
<link href="/style/main.css" type="text/css" rel="stylesheet" />
<link rel="stylesheet" href="/style/searchProcess.css" type="text/css">
<link rel="shortcut icon" href="/pic/2GAR61236.gif"/>
</head>
<body>

 <div id="main">
<!--head-->
 <div id="header"></div><!--头部logo和banner所在的部分-->
 <div id="menu">
    <div class="menulist">
	  <div class="menucontent">
	    <ul id="nav">
	      <li><a href="/index.html">HOME   </a></li>
		  <li>|</li>  
		  <li><a href="/php/index.php">个人主页</a></li>
		  <li>|</li> 
		  <li><a href="/labAchievement/index.html">学术成果</a></li> 
		  <li>|</li>   
		  <li><a href="/phpExcelReader/classification.html">期刊会议</a></li> 
		  <li>|</li>  
		  <li><a href="/weeklyReport/index.html">周报</a></li>
		  <li>|</li>  
		  <li><a href="/upload.html">公共资源</a></li>
		</ul>  
	  </div>
	</div>
	<div class="menuleft"></div>
	<div class="clear"></div>
  </div><!--end of menu-->

    <div id="maincontent">
	<span style='float:right'><a  style="color:red" href="./searchResult/result.xlsx" download="result.xlsx">导出表格</a></span>
	<title>搜索结果</title>
	<h1>搜索结果</h1>
<?php
//ini_set('display_errors', TRUE);
//ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Asia/Shanghai');
define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');
error_reporting(E_ALL);
/** Include path **/
set_include_path(get_include_path() . PATH_SEPARATOR . './Classes/');
/** Include PHPExcel */
require_once dirname(__FILE__) . '/Classes/PHPExcel.php';//downloading
/** PHPExcel_IOFactory */
include 'PHPExcel/IOFactory.php';
include 'PHPExcel/Worksheet/ColumnDimension.php';
$inputFileType = 'Excel5';
//	$inputFileType = 'Excel2007';

//write
$WriteExcel = new PHPExcel();

include 'mySqlSetup.php';//数据库设置

//连接mysql
@$link = new mysqli($HOST,$USER,$PASSWD,$DATABASE);
if($link->connect_error){
	echo "Connecting failed:".$link->error."<br/>";
	exit;
}

function QueryMysql($link,$sql)
{
	$ret=$link->query($sql);
	if(!$ret){
		echo "Query failed:".$link->erro."<br/>";
	}
	return $ret;
}

QueryMysql($link,"set names utf8");

//mysql_select_db($DATABASE,$link) or die("选择数据库失败！");
//mysql_query('set names utf8') or die("设置数据库语言失败");

/*format and properties*/
function setExcel()
{

	$WriteExcel=$GLOBALS['WriteExcel'];
		// Set document properties
		$WriteExcel->getProperties()->setCreator("fj")
							 ->setLastModifiedBy("fj")
							 ->setTitle("Search result")
							 ->setSubject("Paper");
//设置表头格式
$styleArrayHead = array(
'font' => array(
		'bold' => true,
	),
'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,),
'borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_MEDIUM,
'color' => array('argb' => '00000000')
,),),);
		
		//表头设置
		 $WriteExcel->setActiveSheetIndex(0)
				    ->setCellValue('B2',"英文题名")
				    ->setCellValue('C2',"中文题目")
					->setCellValue('D2',"原文语言类型（中文/英文）")
					->setCellValue('E2',"作者英文名")
					->setCellValue('F2',"作者中文名")
					->setCellValue('G2',"发表类型（期刊/会议）")
					->setCellValue('H2',"刊物名称（中文）")
					->setCellValue('I2',"刊物名称（英文）")
					->setCellValue('J2',"会议名称")
					->setCellValue('K2',"发表时间")
			        ->setCellValue('L2',"会议地点")
				    ->setCellValue('M2',"页码")
				    ->setCellValue('N2',"SCI检索号")
					->setCellValue('O2',"EI检索号")
					->setCellValue('P2',"ISTP检索号")
					->setCellValue('Q2',"影响因子")
					->setCellValue('R2',"他引次数")
					->setCellValue('S2',"书名信息")
					->setCellValue('T2',"负责老师")
					->setCellValue('A2',"序号")->getStyle('A2')->applyFromArray($styleArrayHead);;//->setWidth(8);
		//设置表格的宽度
		 for($i='B';$i<'U';$i++)
		{
			 $WriteExcel->setActiveSheetIndex(0)->getStyle($i.'2')->applyFromArray($styleArrayHead);
			 $WriteExcel->setActiveSheetIndex(0)->getColumnDimension($i)->setWidth(28);//setAutoSize(true);
		}
}

//整理显示的格式
function str_order($col_array)
{
	$ans="";
	//var_dump($col_array);
	//Reynolds D A, Quatieri T F, Dunn R B.  Speaker verification using adapted Gaussian mixture models[J].  Digital signal processing, 2000, 10(1): 19-41.
	$ans.=!empty($col_array["作者中文名"])?$col_array["作者中文名"].".":($col_array["作者英文名"].".");
	$ans.=!empty($col_array["中文题目"])?$col_array["中文题目"]:$col_array["英文题目"];
	if(!empty($col_array["刊物名称（英文）"]))
		$ans.="[J].".$col_array["刊物名称（英文）"];
	if(!empty($col_array["会议名称"]))
		$ans.=".".$col_array["会议名称"];
	$tmp=$col_array["发表时间"];
	if(!empty($tmp))//年
	{
		sscanf($tmp,"%d",$year);
		if(!empty($col_array["会议名称"]))//会议
		{
			$ans.=",".(string)$year;
		}
		else//期刊
		{
			$ans.=".".(string)$year;
			$vol=strpbrk($tmp,"，,");
			$vol=strpbrk($vol,"123456789");
			if($vol)//卷
			{
				sscanf($vol,"%d",$vol_value);
				$isu_str=strpbrk($vol,",，");
				$isu_str=strpbrk($isu_str,"123456789");
				if($isu_str)
				{
					$ans.=",".(string)$vol_value;//期
					sscanf($isu_str,"%d",$isu);
					$ans.="(".(string)$isu.")";
					$tmp=$col_array["页码"];
					$page_str=strpbrk($tmp,"123456789");
					if($page_str)
					$ans.=":".$page_str;
				}
			}
		}
	}
	$ans.=".";
	return $ans;
}
 

function year_between($str)
{
		sscanf($_POST["year_start"],"%d-%d-%d",$year_begin,$month_begin,$day_begin);
		sscanf($str,"%d年%d月",$year,$month);
		sscanf($_POST["year_end"],"%d-%d-%d",$year_end,$month_end,$day_end);
		//for debug
		/*
		echo $str."<br/>";
		echo "yb:".$year_begin."mb:".$month_begin."ye:".$year_end."me:".$month_end."<br/>";
		echo "y:".$year."  m:".$month."<br/>";*/
		if(($year>$year_begin || ($year==$year_begin && $month>=$month_begin)) && ($year<$year_end || ($year==$year_end && $month<=$month_end)))
			return true;
		else
			return false;

}

//按搜索条件显示
function searchMysql($link) 
{
	$conditions="select * from `$GLOBALS[TABLE]` where ";
	$and_flag=false;
	//for debug
	
	//echo "检索条件"."<br/>"."分区:".$_POST["dist"]."领域:".$_POST["area"]."检索类型:".$_POST["index_type"]."论文类型:".$_POST["paper_type"]."影响因子//:".$_POST["infec"]."<br/>";
	//echo "发表时间：".$_POST[year_start]."到".$_POST["year_end"]."关键字：".$_POST["key_word"]."<br/>";
	//分区
	if(!empty($_POST["dist"]) && $_POST["dist"]!="all")
	{
	//	$and_flag=true;
	}
	//领域
	if(!empty($_POST["area"]) && $_POST["area"]!="all")
	{
	//	$and_flag=true;
	}
	//检索类型
	if(!empty($_POST["index_type"]) && $_POST["index_type"]!="all")
	{
		if($and_flag)
		$conditons.="AND(";	
		if($_POST["index_type"]=="SCI")
		$conditions.="`SCI检索号` != '' ";
		else
		$conditions.="`EI检索号` != ''";
		if($and_flag)
		$conditions.=") ";
		else
		$and_flag=true;
	}
	//论文类型
	if(!empty($_POST["paper_type"]) && $_POST["paper_type"]!="all")
	{
		if($and_flag)
		$conditions.="AND(";

		if($_POST["paper_type"]=="jou")
		$conditions.="`发表类型（期刊/会议）` =  '期刊' ";
		else
		$conditions.="`发表类型（期刊/会议）` =  '会议' ";
		if($and_flag)
		$conditions.=") ";
		else
		$and_flag=true;
	}
	//影响因子
	if(!empty($_POST["infec"]) && $_POST["infec"]!="all")
	{
	//	$and_flag=true;
	}
	//发表时间
	if(!empty($_POST["year_start"]) || !empty($_POST["year_end"]))
	{
		//done by php

	}
	//关键字
	if(!empty($_POST["key_word"]))
	{
		//不区分大小写的方式
		//$keyword=strtolower("ABCdef");
		//$sql="select * from table_name where lower(title) like '%".$keyword."%'";
		$_POST["key_word"]=strtolower($_POST["key_word"]);
		if($and_flag)
		$conditions.="AND(";

		$conditions.="lower(`中文题目`) LIKE  '%$_POST[key_word]%' OR ";
		$conditions.="lower(`英文题目`) LIKE  '%$_POST[key_word]%' OR ";
		$conditions.="lower(`原文语言类型（中文/英文）`) LIKE  '%$_POST[key_word]%' OR ";
		$conditions.="lower(`作者英文名`) LIKE  '%$_POST[key_word]%' OR ";
		$conditions.="lower(`作者中文名`) LIKE  '%$_POST[key_word]%' OR ";
		$conditions.="lower(`刊物名称（中文）`) LIKE  '%$_POST[key_word]%' OR ";
		$conditions.="lower(`刊物名称（英文）`) LIKE  '%$_POST[key_word]%' OR ";
		$conditions.="lower(`会议名称`) LIKE  '%$_POST[key_word]%' OR ";
		$conditions.="lower(`会议地点`) LIKE  '%$_POST[key_word]%' OR ";
		$conditions.="lower(`SCI检索号`) LIKE  '%$_POST[key_word]%' OR ";
		$conditions.="lower(`EI检索号`) LIKE  '%$_POST[key_word]%' OR ";
		$conditions.="lower(`ISTP检索号`) LIKE  '%$_POST[key_word]%' OR ";
		$conditions.="lower(`署名信息`) LIKE  '%$_POST[key_word]%' OR ";
		$conditions.="lower(`责任教师`) LIKE  '%$_POST[key_word]%' ";
		if($and_flag)
		$conditions.=") ";
		else
		$and_flag=true;
	}
	if(!$and_flag)//如果一个条件都没指定
	$conditions.="1 ";
	$conditions.=";";
	//for debug
	//echo $conditions."<br/>";
	return QueryMysql($link,$conditions);
}
//保存到Excel
function saveToExcel($result)
{
	//设置Excel单元格边框
	$styleArray = array(
	'alignment' => array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_LEFT),
	'borders' => array('outline' => array('style' => PHPExcel_Style_Border::BORDER_THIN,
	'color' => array('argb' => '00000000')
	,),),);
		setExcel();
		$WriteExcel=$GLOBALS['WriteExcel'];
		$contentindex=1;//序号
		$excelRowIndex="3";//从excel的第三列开始写数据
		while($row = $result->fetch_row())
			{
				
				if(!empty($_POST["year_start"]) && !empty($_POST["year_end"])){
					if(!year_between($row[10])){
						continue;
					}
				}
				$excelColumnIdex='A';
				//序号
				$WriteExcel->setActiveSheetIndex(0)
					->setCellValue($excelColumnIdex.$excelRowIndex,(string)($contentindex++));//内容
				$WriteExcel->setActiveSheetIndex(0)->getStyle($excelColumnIdex.$excelRowIndex)->applyFromArray($styleArray);//设置边框
				//设置数字的显示格式和表格的高度
				$WriteExcel->setActiveSheetIndex(0)->getStyle('O'.(string)$contentindex)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
				$WriteExcel->getActiveSheet()->getRowDimension($contentindex)->setRowHeight(30);
				
				//var_dump($row);
				for($excelColumnIdex='B',$i=1;$i<count($row)-1;++$i)// 简介和数据库中的序号不用写入excel
				{
					//for debug
				//	echo $excelColumnIdex.$excelRowIndex.":$row[$i]<br/>";
					$WriteExcel->setActiveSheetIndex(0)
					->setCellValue($excelColumnIdex.$excelRowIndex,$row[$i]);
				
					$WriteExcel->setActiveSheetIndex(0)->getStyle($excelColumnIdex.$excelRowIndex)->applyFromArray($styleArray);//设置边框
					$excelColumnIdex++;
				}
				$excelRowIndex++;
			}

			$WriteExcel->setActiveSheetIndex(0);
			$objWriter = PHPExcel_IOFactory::createWriter($WriteExcel, 'Excel2007');
			//echo __FILE__."<br/>";
			$name="./searchResult/result.xlsx";//file name
			$objWriter->save($name);//str_replace('.php', '.xlsx', __FILE__)
}
function disOnWeb($result)
{
	if(is_object($result)){//合理检索
		$lines= $result->num_rows;
		if($lines)
		{	
			$dis="<ul>";
			while($row = $result->fetch_assoc())
				{
					
					if(!empty($_POST["year_start"]) && !empty($_POST["year_end"])){
						if(!year_between($row[10])){
							$lines-=1;
							continue;
						}
					}
					//display on web
					$abstractCont=$row["abstract"];//save abstract
					$paperUrl="#";//remain to be changed
					$dis.="<li class=\"li_result\">
							<div class=\"download\"><a href=$paperUrl><img class=\"pdfIcon\" src=\"/images/pdfIcon.jpg\"></img></a></div>";
					$dis.=str_order($row);
					//$dis.="<br/><b>简介：</b>".$abstractCont."<hr/></li><br/>";
					$dis.="<hr/></li><br/>";
					
				}
				$dis.="</ul>";
			
		}
		if($lines)
		{
			echo $lines."条记录"."<br/><br/>";
			echo $dis;
		}
		else
		{
			echo "检索结果为空...";
		}
	}
	else
	{
		echo "检索结果不是对象<br/>";
	}
}

$result=searchMysql($link);
disOnWeb($result);
$result->data_seek(0);
saveToExcel($result);

?>
<!--footer-->
   <div class="footer">
      <div class="footerleft"></div>
	  <div class="footercontent">
	     <div class="footercontentleft">Welcome to ******laboratory</div>
		 <div class="footercontentright">2014 International</div>
	  </div>
	  <div class="footerright"></div>
	</div>	
</body>
</html>